import baostock as bs
import datetime
import pandas as pd
import pymysql

import time
from dateutil import parser

nowTime = datetime.datetime.now().strftime('%Y-%m-%d')
lastTime = (datetime.datetime.now() - datetime.timedelta(days=3000)).strftime('%Y-%m-%d')

# db = pymysql.Connect(
#     host='127.0.0.1',
#     port=3306,
#     user='root',
#     passwd='',
#     db='gupiao',
#     charset='utf8'
# )
db = pymysql.Connect(
    host='apiccr.megowx.com',
    port=3306,
    user='gupiao',
    passwd='Aitrip123',
    db='gupiao',
    charset='utf8'
)
cursor = db.cursor()

# date,code,open,high,low,close,preclose,volume,amount,adjustflag,turn,tradestatus,pctChg,peTTM,pbMRQ,psTTM,pcfNcfTTM,isST
sql_dk = "INSERT INTO `fa_stock_k` (`date`, `code`, `open`, `high`, `low`, `close`, " \
         "`preclose`, `volume`, `amount`, `adjustflag`, `turn`, `tradestatus`, `pctChg`, `peTTM`, `pbMRQ`, `psTTM`, " \
         "`pcfNcfTTM`, `isST`, `MA5`, `MA10`, `MA20`, `MA30`, `MA89`, `MA250`," \
         "`X20`, `B5`, `B10`, `pctChg3`, `pctChg6`, `volume3`, `volume6`," \
         "`LU5`, `LU10`, `LU20`, `LU30`, `LU89`, `LU250` ) VALUES"

sql_mk = "INSERT INTO `fa_stock_mk` (`date`, `code`, `open`, `high`, `low`, `close`, " \
         "`volume`, `amount`, `adjustflag`, `turn`, `pctChg`) VALUES	"

# 是否部分更新
isbfgx = 0
# 查询最新的K线日期
try:
    sql0 = "SELECT date FROM `fa_stock_k` WHERE 1 ORDER BY id DESC LIMIT 1 "
    result = cursor.execute(sql0)
    db.commit()
    if result:
        date = cursor.fetchone()
        lastTime = (datetime.datetime(year=date[0].year, month=date[0].month, day=date[0].day)
                    + datetime.timedelta(days=1)).strftime('%Y-%m-%d')
        isbfgx = 1
        # lastTime = str(date[0].year) + "-{:0>2d}".format(date[0].month) + "-{:0>2d}".format(date[0].day)
except Exception as e:
    print(e)


# INSERT INTO `fa_stock_k` (`date`, `code`, `open`, `high`, `low`, `close`, `preclose`, `volume`, `amount`, `adjustflag`, `turn`, `tradestatus`, `pctChg`, `peTTM`, `pbMRQ`, `psTTM`, `pcfNcfTTM`, `isST`) VALUES
# ('2019-03-18','sh.600000',11.4500,11.6000,11.3700,11.5900,11.4300,43879562,503915219.0000,'3',0.156134,'1',1.399824,6.084176,0.799606,1.983133,3.274054,'0')
# str1 = ','.join(data)

def caculatma(datalist, j, days):
    if len(datalist) < 1 or j >= len(datalist) or days < 5 or j <= days:
        return 0
    ma = 0
    for k in range(j - days, j):
        ma += float(datalist[k][5])

    return ma / days


# 有了id
def caculatma2(datalist, j, days):
    if len(datalist) < 1 or j >= len(datalist) or days < 5 or j <= days:
        return 0
    ma = 0
    for k in range(j - days, j):
        ma += float(datalist[k][6])

    return ma / days


def getLU(datalist, j, day):
    if len(datalist) < 1 or j >= len(datalist) or j <= day:
        return 0

    k = 0
    if day == 5:
        k = 19
    elif day == 10:
        k = 20
    elif day == 20:
        k = 21
    elif day == 30:
        k = 22
    elif day == 89:
        k = 23
    elif day == 250:
        k = 24

    MA = float(datalist[j][k])

    if k == 0 or MA == 0:
        return 0

    # 确定今日的LU
    LU = 0
    if float(datalist[j][6]) > MA:
        LU += 1
    else:
        LU -= 1

    i = j - 1
    while i >= 0:
        if LU > 0:
            if float(datalist[i][6]) > float(datalist[i][k]):
                LU += 1
            else:
                break
        else:
            if float(datalist[i][6]) < float(datalist[i][k]):
                LU -= 1
            else:
                break
        i -= 1

    return LU


def getClosebydate(code1, date1, year):
    startdate = parser.parse(date1)
    if year == 5:
        startdate = startdate - datetime.timedelta(days=1830)
    else:
        startdate = startdate - datetime.timedelta(days=3655)

    enddate = startdate + datetime.timedelta(days=10)

    rs1 = bs.query_history_k_data_plus(code1,
                                       "date,code,close",
                                       start_date=startdate.strftime('%Y-%m-%d'), end_date=enddate.strftime('%Y-%m-%d'),
                                       frequency="m", adjustflag="2")
    h = 0
    while (rs1.error_code == '0') & rs1.next():
        one1 = rs1.get_row_data()
        h = float(one1[2])
        break
    return h


def getClosebydate2(code1, date, year):
    h = 0
    try:
        startdate = datetime.datetime(year=date.year, month=date.month, day=date.day)
        if year == 5:
            startdate = startdate - datetime.timedelta(days=1830)
        else:
            startdate = startdate - datetime.timedelta(days=3655)

        enddate = startdate + datetime.timedelta(days=31)

        sql = "SELECT close FROM `fa_stock_mk` where date > '" + startdate.strftime(
            '%Y-%m-%d') + "' and date < '" + enddate.strftime('%Y-%m-%d') + "' and code = '" + code1 + "' limit 1"
        result = cursor.execute(sql)

        if result:
            dataone = cursor.fetchone()
            h = float(dataone[0])
    except Exception as e:
        print(e)

    return h


def setMA(code, limit):
    try:
        sql = "SELECT * FROM `fa_stock_k` WHERE code='" + code + "' ORDER BY id desc limit " \
              + str(limit)
        result = cursor.execute(sql)
        if result:
            datalist = cursor.fetchall()

            # 元组逆序
            datalist = datalist[::-1]
            for j in range(250, len(datalist)):
                data = datalist[j]
                ma5 = caculatma2(datalist, j, 5)
                ma10 = caculatma2(datalist, j, 10)
                ma20 = caculatma2(datalist, j, 20)
                ma30 = caculatma2(datalist, j, 30)
                ma89 = caculatma2(datalist, j, 89)
                ma250 = caculatma2(datalist, j, 250)
                x20 = float(data[6]) / ma20

                # "2015-01-30"
                h5 = getClosebydate2(data[2], data[1], 5)
                # "2010-01-29"
                h10 = getClosebydate2(data[2], data[1], 10)
                b10 = b5 = 0
                if h5 > 0:
                    b5 = float(data[6]) / h5
                if h10 > 0:
                    b10 = float(data[6]) / h10

                if j > 3:
                    pctchg3 = float(data[6]) - float(datalist[j - 3][6])
                    volume3 = (float(datalist[j - 3][8]) + float(datalist[j - 2][8]) + float(datalist[j - 1][8])) / 3
                if j > 6:
                    pctchg6 = float(data[6]) - float(datalist[j - 6][6])
                    volume6 = (float(datalist[j - 6][8]) + float(datalist[j - 5][8]) + float(
                        datalist[j - 4][8]) + float(datalist[j - 3][8]) + float(datalist[j - 2][8]) + float(
                        datalist[j - 1][8])) / 6

                sql = "UPDATE fa_stock_k SET MA5=" + str(ma5) + ", MA10=" + str(ma10) \
                      + ", MA20=" + str(ma20) + ", MA30=" + str(ma30) \
                      + ", MA89=" + str(ma89) + ", MA250=" + str(ma250) + ", X20=" + str(x20) \
                      + ", B5=" + str(b5) + ", B10=" + str(b10) + ", pctChg3=" + str(pctchg3) \
                      + ", pctChg6=" + str(pctchg6) + ", volume3=" + str(volume3) + ", volume6=" + str(volume6) \
                      + " WHERE id=" + str(data[0])
                cursor.execute(sql)

    except Exception as e:
        print(e)
        print(sql)


def setLU(datalist):
    global isbfgx

    begin = 0
    if isbfgx == 1:
        datalist = datalist[::-1]
        begin = 250

    try:
        for j in range(begin, len(datalist)):
            data = datalist[j]
            LU5 = getLU(datalist, j, 5)
            if LU5 == 0:
                continue
            LU10 = getLU(datalist, j, 10)
            LU20 = getLU(datalist, j, 20)
            LU30 = getLU(datalist, j, 30)
            LU89 = getLU(datalist, j, 89)
            LU250 = getLU(datalist, j, 250)

            sql = "UPDATE fa_stock_k SET LU5=" + str(LU5) + ", LU10=" + str(LU10) + ", LU20=" + str(LU20) \
                  + ", LU30=" + str(LU30) + ", LU89=" + str(LU89) + ", LU250=" + str(LU250) \
                  + " WHERE id=" + str(data[0])
            cursor.execute(sql)

    except Exception as e:
        print(e)
        print(sql)

    return True


def saveK2DB(datalist, isDayk):
    global cursor
    global db
    global isbfgx

    if len(datalist) < 1:
        return False

    sql = sql_mk
    if isDayk == 1:
        sql = sql_dk

    try:
        for j in range(0, len(datalist)):
            if j > 0:
                sql += ","
            data = datalist[j]
            for i in range(0, len(data)):
                item = data[i]
                if len(data[i]) < 1:
                    item = "0"
                if i == 0:
                    str1 = "'" + item + "'"
                else:
                    str1 += ",'" + item + "'"
            if isDayk == 1:
                ma5 = ma10 = ma20 = ma30 = ma89 = ma250 = x20 = b5 = b10 = pctchg3 = pctchg6 = volume3 = volume6 = 0
                LU5 = LU10 = LU20 = LU30 = LU89 = LU250 = 0
                if isbfgx == 0:
                    ma5 = caculatma(datalist, j, 5)
                    ma10 = caculatma(datalist, j, 10)
                    ma20 = caculatma(datalist, j, 20)
                    ma30 = caculatma(datalist, j, 30)
                    ma89 = caculatma(datalist, j, 89)
                    ma250 = caculatma(datalist, j, 250)
                    x20 = 0
                    if ma20 > 0:
                        x20 = float(data[5]) / ma20

                    # "2015-01-30"
                    h5 = getClosebydate(data[1], data[0], 5)
                    # "2010-01-29"
                    h10 = getClosebydate(data[1], data[0], 10)

                    b5 = b10 = 0
                    if h5 > 0:
                        b5 = float(data[5]) / h5
                    if h10 > 0:
                        b10 = float(data[5]) / h10
                    pctchg3 = pctchg6 = volume3 = volume6 = LU5 = LU10 = LU20 = LU30 = LU89 = LU250 = 0
                    if j > 3:
                        pctchg3 = float(data[5]) - float(datalist[j - 3][5])
                        volume3 = (float(datalist[j - 3][7]) + float(datalist[j - 2][7]) + float(
                            datalist[j - 1][7])) / 3
                    if j > 6:
                        pctchg6 = float(data[5]) - float(datalist[j - 6][5])
                        volume6 = (float(datalist[j - 6][7]) + float(datalist[j - 5][7]) + float(
                            datalist[j - 4][7]) + float(datalist[j - 3][7]) + float(datalist[j - 2][7]) + float(
                            datalist[j - 1][7])) / 6

                str1 += ",'" + str(ma5) + "'"
                str1 += ",'" + str(ma10) + "'"
                str1 += ",'" + str(ma20) + "'"
                str1 += ",'" + str(ma30) + "'"
                str1 += ",'" + str(ma89) + "'"
                str1 += ",'" + str(ma250) + "'"
                str1 += ",'" + str(x20) + "'"
                str1 += ",'" + str(b5) + "'"
                str1 += ",'" + str(b10) + "'"
                str1 += ",'" + str(pctchg3) + "'"
                str1 += ",'" + str(pctchg6) + "'"
                str1 += ",'" + str(volume3) + "'"
                str1 += ",'" + str(volume6) + "'"
                str1 += ",'" + str(LU5) + "'"
                str1 += ",'" + str(LU10) + "'"
                str1 += ",'" + str(LU20) + "'"
                str1 += ",'" + str(LU30) + "'"
                str1 += ",'" + str(LU89) + "'"
                str1 += ",'" + str(LU250) + "'"

            sql += "(" + str1 + ")"

        sql = sql.replace('nan', 'null').replace('None', 'null').replace('none', 'null')
        cursor.execute(sql)

        # 设置LU
        if isDayk == 1:
            code = datalist[0][1]
            if isbfgx == 0:
                # 仅设置LU
                sql = "SELECT * FROM `fa_stock_k` WHERE code='" + code + "'"
            else:
                # 部分更新 往前取250天
                limit = len(datalist) + 251
                # 先设置MA
                setMA(code, limit)
                db.commit()
                sql = "SELECT * FROM `fa_stock_k` WHERE code='" + code + "' ORDER BY id desc limit " \
                      + str(limit)

            result = cursor.execute(sql)
            if result:
                setLU(cursor.fetchall())

        db.commit()
    except Exception as e:
        print(e)
        print(sql)
        return False

    return True


def queryKdata(code):
    global nowTime
    global lastTime, isbfgx
    global sql_dk

    try:
        sql0 = "SELECT date FROM `fa_stock_k` WHERE code = '" + code + "' ORDER BY id DESC LIMIT 1 "
        result = cursor.execute(sql0)
        db.commit()
        if result:
            date = cursor.fetchone()
            lastTime = (datetime.datetime(year=date[0].year, month=date[0].month, day=date[0].day)
                        + datetime.timedelta(days=1)).strftime('%Y-%m-%d')
            isbfgx = 1
    except Exception as e:
        print(e)

    rs = bs.query_history_k_data_plus(code,
                                      "date,code,open,high,low,close,preclose,volume,amount,adjustflag,turn,"
                                      "tradestatus,pctChg,peTTM,pbMRQ,psTTM,pcfNcfTTM,isST",
                                      start_date=lastTime, end_date=nowTime,
                                      frequency="d", adjustflag="2")  # frequency="d"取日k线，adjustflag="3"默认不复--前复权-2
    data_list = []
    while (rs.error_code == '0') & rs.next():
        data_list.append(rs.get_row_data())

    if len(data_list) < 1:
        return 0

    saveK2DB(data_list, 1)
    return 1


def queryMKdata(code):
    # 没有年K，最大月K -- 跑一次就好了
    global nowTime
    global lastTime

    rs = bs.query_history_k_data_plus(code,
                                      "date,code,open,high,low,close,volume,amount,adjustflag,turn,pctChg",
                                      start_date="2001-04-01", end_date=nowTime,
                                      frequency="m", adjustflag="2")  # frequency="d"取日k线，adjustflag="3"默认不复--前复权-2
    data_list = []
    while (rs.error_code == '0') & rs.next():
        data_list.append(rs.get_row_data())

    saveK2DB(data_list, 0)
    return True


def insertBase2DB(datalist):
    global cursor
    global db

    sql = "INSERT INTO `fa_stock_base` (`code`, `code_name`) VALUES	"

    for i in range(0, len(datalist)):
        if i > 0:
            sql += ","
        sql += "('" + datalist[i][0] + "','" + datalist[i][2] + "')"

    try:
        cursor.execute(sql)
        db.commit()
    except Exception as e:
        print(e)
        # 如果以
        return False

    return True


#### 登陆系统 ####
lg = bs.login()
# 显示登陆返回信息
print('login respond error_code:' + lg.error_code)
print('login respond  error_msg:' + lg.error_msg)

### 获取证券信息 ####
rs = bs.query_all_stock(day="2019-04-30")
print('query_all_stock respond error_code:' + rs.error_code)
print('query_all_stock respond  error_msg:' + rs.error_msg)

#### 打印结果集 ####
# sh.600000～sz.300773
num = 0
isFirst = 0
data_list = []
while (rs.error_code == '0') & rs.next():
    one = rs.get_row_data()
    if isFirst == 0 and one[0] == "sh.600000":
        isFirst = 1

    if isFirst == 1 or num == 0:
        result = queryKdata(one[0])
        if result == 0:
            num += 1
            continue
        # if num == 74:
        #     print('--' + str(num))
        # queryMKdata(one[0])
        print('----Finish---' + str(num))
        # data_list.append(one)
        num += 1
        if one[0] == "sz.300773":
            break
        # if num > 2:
        #     break

#### 登出系统 ####
bs.logout()

cursor.close()
db.close()

print('----OK---')
